import random

from core.models import Rating, Restaurant, Sale, Staff, StaffRestaurant
from django.contrib.auth.models import User
from django.utils import timezone
from django.db.models import Avg, Value, CharField, Sum, Count, F, Q, Case, When, Min, Max, OuterRef, Subquery, Exists
from django.db.models.functions import Lower, Concat, Coalesce
import itertools


def run():
    sales = Sale.objects.filter(restaurant__restaurant_type__in=['IN', 'CH'])
    print(sales)
    print(sales.values_list('restaurant__restaurant_type').distinct())

    restaurants = Restaurant.objects.filter(restaurant_type__in=['IN', 'CH'])
    sales = Sale.objects.filter(restaurant__in=Subquery(restaurants.values('pk')))
    print(sales.count())
    sales = Sale.objects.filter(restaurant_id__in=Subquery(restaurants.values('pk')))
    print(sales.count())
    sales = Sale.objects.filter(restaurant__in=restaurants.values_list('pk', flat=True))
    print(sales.count())
    sales = Sale.objects.filter(restaurant_id__in=restaurants.values('pk'))
    print(sales.count())
    """
    SELECT id,
       name,
       restaurant_type,
       (SELECT income
        FROM core_sale
        WHERE restaurant_id = core_restaurant.id
        ORDER BY datetime DESC
        LIMIT 1) AS last_sale
    FROM core_restaurant;
    """
    restaurants = Restaurant.objects.all()
    print('=-========33333')
    sales = Sale.objects.filter(restaurant_id=OuterRef('id')).order_by('-datetime')
    # outer query
    restaurants = restaurants.annotate(
        last_sale_income=Subquery(sales.values('income')[:1]),
        last_sale_expenditure=Subquery(sales.values('expenditure')[:1]),
        profit=F('last_sale_income') - F('last_sale_expenditure'),
    )
    for restaurant in restaurants:
        print(restaurant.name, restaurant.last_sale_income, restaurant.last_sale_expenditure, restaurant.profit)

    # filter to restaurants that have any sales with income > 85
    sub_query = Sale.objects.filter(restaurant__id=OuterRef('pk'), income__gt=85)
    exists = Exists(sub_query)
    restaurants = Restaurant.objects.annotate(
        has_sales=exists,
    ).filter(has_sales=True)
    restaurants = Restaurant.objects.filter(exists)
    print(restaurants.count())

